using System.Data;
using MySqlConnector;

namespace Front._1118;

public partial class BookForm : Form
{
    public BookForm()
    {
        InitializeComponent();
    }

    private void BookForm_Load(object sender, EventArgs e)
    {
        //查询所有图书
        GetAllBooks();
        
        //查询所有分类
        GetAllCategories();
    }

    private void GetAllCategories()
    {
        using var connection = new MySqlConnection("server=localhost;port=3306;database=book;user=root;password=123456;charset=utf8mb4;AllowPublicKeyRetrieval=True;");
        connection.Open();

        string sql = "SELECT DISTINCT book_category FROM book_info ";
        //创建MysqlDataAdapter
        MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
        //创建DataSet
        DataSet dataSet = new DataSet();
        //填充数据
        mySqlDataAdapter.Fill(dataSet, "book_category");
        if (dataSet.Tables["book_category"].Rows.Count > 0)
        {
            DataTable categoryTable = dataSet.Tables["book_category"];
            // 插入一行全部
            DataRow row = categoryTable.NewRow();
            row[0] = "全部";
             categoryTable.Rows.InsertAt(row,0);
            
            comboBox1.DataSource = categoryTable;
            comboBox1.ValueMember = categoryTable.Columns[0].ColumnName;
            
            
        }
    }

    private void GetAllBooks()
    {
        using var connection = new MySqlConnection("server=localhost;port=3306;database=book;user=root;password=123456;charset=utf8mb4;AllowPublicKeyRetrieval=True;");
        connection.Open();

        string sql = "SELECT book_id,isbn,book_name FROM book_info ";
        //创建MysqlDataAdapter
        MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
        //创建DataSet
        DataSet dataSet = new DataSet();
        //填充数据
        mySqlDataAdapter.Fill(dataSet, "book_info");
        this.dataGridView1.DataSource =  dataSet.Tables["book_info"];
    }                  
    
    private void ComboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (comboBox1.SelectedItem != null)
        {
            string selectedCategory = comboBox1.SelectedValue?.ToString();
            GetBooksByCategory(selectedCategory);
        }
    }

// 根据分类查询图书的方法
    private void GetBooksByCategory(string category)
    {
        try
        {
            using var connection = new MySqlConnection("server=localhost;port=3306;database=book;user=root;password=123456;charset=utf8mb4;AllowPublicKeyRetrieval=True;");
            connection.Open();
        
            string sql;
            MySqlCommand command;
        
            if (category == "全部")
            {
                sql = "SELECT * FROM book_info;";
                command = new MySqlCommand(sql, connection);
            }
            else
            {
                sql = "SELECT * FROM book_info WHERE book_category = @Category;";
                command = new MySqlCommand(sql, connection);
                command.Parameters.AddWithValue("@Category", category);
            }
        
            MySqlDataAdapter adapter = new MySqlDataAdapter(command);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
        
            // 将查询结果绑定到DataGridView或其他控件
            dataGridView1.DataSource = dataTable;
        
            // 或者如果你使用的是其他控件，比如ListBox
            // listBox1.DataSource = dataTable;
            // listBox1.DisplayMember = "book_title"; // 假设有书名字段
        }
        catch (Exception ex)
        {
            MessageBox.Show($"查询图书时出错: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

    private void button2_Click(object sender, EventArgs e)
    {
        // 实例化新增窗体
        addBookForm addForm = new addBookForm();
        DialogResult result = addForm.ShowDialog();
        if (result == DialogResult.OK)
        {
            //刷新
            GetAllBooks();
        }
    }

    private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
    {
        //获取选中的行
        DataGridViewRow row = this.dataGridView1.SelectedRows[0];
        int bookId = (int)row.Cells[0].Value;
        DialogResult result = MessageBox.Show("确定删除此条数据吗？",caption:"警告",MessageBoxButtons.OKCancel);
        if (result == DialogResult.OK)
        {
            //确定删除
            using var connection = new MySqlConnection("server=localhost;port=3306;database=book;user=root;password=123456;charset=utf8mb4;AllowPublicKeyRetrieval=True;");
            connection.Open();

            string sql = "delete from book_info where book_id=@book_id ";
            using var command = new MySqlCommand(sql, connection);
            //给sql 变量填充值
            command.Parameters.AddWithValue("@book_id", bookId);
           
            int count = command.ExecuteNonQuery();
            if (count > 0)
            {
                //添加成功
                MessageBox.Show("删除成功");
                //刷新
                GetAllBooks();
            }
            else
            {
            
            }
        }
        else
        {
            
        }
    }
}